<?php
/**
 * @description : all db operation of MstatWebService
 * @class-name : MstatOperationOfDatabase().
 * @author : stephen.mo <stephen.mo@achievo.com>
 * @date : 2010-04-06
 */

class MstatOperationOfDatabase {
    protected $timeless;
    protected $time_now;
    protected $time_str;
    protected $time_sql;
    protected $config;
    protected $reader;
    protected $writer;

	//instance a object.
    public static function getInstance() {
        static $instance = null ;
        return $instance ? ($instance) : ($instance = new MstatOperationOfDatabase()) ;
    }

	//construct the class.
    public function __construct() {
        $this->config = ConfigReaderFactory::getInstance()->getConfigReader(MstatOperationOfDatabaseConfig::MSTAT_DATABASE_CONFIG_FILE_PATH)->getConfig();
	    $this->createReadableRecordFactory();
    }

	//create a handle for reader.
    private function createReadableRecordFactory() {
        $readers = array ();
        foreach ($this->config->db->readers->reader as $reader) {
            $readers[] = (string) $reader;
        }
        $db = $readers[array_rand($readers)];
        $user = (string) $this->config->db->user;
        $pass = (string) $this->config->db->pwd;
        $name = (string) $this->config->db->name;
        $this->reader = new ReadableRecordFactory($db, $user, $pass, $name);
    }
    
	//create a handle for writer.
    private function createWritedableRecordFactory() {
        $writers = array();
        $db = (string)$this->config->db->writer;
	    $user = (string)$this->config->db->user;
        $pass = (string)$this->config->db->pwd;
        $name = (string)$this->config->db->name;
        $this->writer = new WritableRecordFactory($db,$user,$pass,$name);
    }
    
	//set name of table video access.
    private function getTableNameOfVideoAccess() {
        return 'video_access_log';
    }
    
    //set name of table video access -merge table.
    private function getMergeTableNameOfVideoAccessLog($year=null) {
        $year = (empty($year)) ? date('Y') : ((!is_numeric($year)) ? date('Y') : $year);
        $table_prefix = 'video_access_log_merge_';
        return (empty($year)) ? $table_prefix.date('Y') : $table_prefix.$year;
    }

	//set name of table sign in and out.
	private function getTableNameOfSignInOut() {
		return 'member_access_log';
	}
    
    //set name of table fp_video_access.
    private function getTableNameOfFpVideoAccessWS() {
        return 'fp_video_access_ws';
    }

    private function getTableNameOfFpVideoAccess() {
        return 'fp_video_access' ;
    }

    //set name of table fp_page_access.
    private function getTableNameOfFpPageAccess() {
        return 'fp_page_access';
    }
    
   //get video viewing and favour ws stat
   private function getTableNameOfVideoViewFavourStat() {
       return 'fp_video_access_ws_stat';
   }
        

    //get the merge table list for video access from mytv_plus_log db.
    static private function checkMergeTablesList($table_name) {
        $merge_prefix = $table_name.'_merge_';
        $query = "show tables like '".$merge_prefix."%'; ";
	    $reader = self::getInstance()->reader->getDbHandle();
        
        try {
            $statement = $reader->prepare($query);
            $statement->execute();
        }
        catch(Exception $e) {
            return -1;
        }
        
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        $table_name_list = array();
        foreach($result as $key=>$val) {
            foreach($val as $key2=>$val2) {
                $table_name_list[] = $val2;
            }
        }
        return $table_name_list;
    }

    //get the scope of time.
    public function getScopeOfTime($table_name,$start_time,$end_time) {
        $merge_prefix = $table_name.'_merge_';
        $start_year = date('Y',$start_time);
        $end_year = date('Y',$end_time);
        $result = array();
        for ($i=$start_year;$i<=$end_year;$i++) {
            $result[] = $merge_prefix.$i;
        }
        return $result;
    }

    // {{{ public function getPageViewVideoRecordCount($member_id,$start_time,$end_time) {
    public function getPageViewVideoRecordCount($member_id,$start_time,$end_time) {
        $all_tables_list = $this->getScopeOfTime($this->getTableNameOfFpVideoAccess(),$start_time,$end_time);
        if (empty($all_tables_list)) return false;

        $all_exist_tables = self::checkMergeTablesList($this->getTableNameOfFpVideoAccess());
        if (empty($all_exist_tables)) return false;
        $query = '';
        $union_str = ' UNION ALL ';
        foreach($all_tables_list as $key=>$val) {
            if (in_array($val,$all_exist_tables)) {
                $query .= "SELECT video_id FROM ".$val.
                         " WHERE member_id='".$member_id."' AND UNIX_TIMESTAMP(play_time)>=".$start_time." AND UNIX_TIMESTAMP(last_alive_time)<=".$end_time.
                         " GROUP BY video_id ";
                $query .= $union_str;
            }
        }

        if (empty($query)) return 0;
        $query = substr($query,0,(strlen($query)-strlen($union_str)));
            $reader = $this->reader->getDbHandle();

        try {
            $statement = $reader->prepare($query);
            $statement->execute();
        }
        catch(Exception $e) {
            return -1;
        }
        $result = $statement->fetch(PDO::FETCH_ASSOC);
        return ($statement->rowCount()>0) ? $statement->rowCount() : 0 ;
    }
        // }}}

	//get the number of records from video access.
    public function getViewVideoRecordCount($member_id,$start_time,$end_time) {

        $all_tables_list = $this->getScopeOfTime($this->getTableNameOfVideoAccess(),$start_time,$end_time);
        if (empty($all_tables_list)) return false;
        
        $all_exist_tables = self::checkMergeTablesList($this->getTableNameOfVideoAccess());
        if (empty($all_exist_tables)) return false;
        $query = '';
        $union_str = ' UNION ALL ';
        foreach($all_tables_list as $key=>$val) {
            if (in_array($val,$all_exist_tables)) {
                $query .= "SELECT video_id FROM ".$val.
                         " WHERE member_id='".$member_id."' AND UNIX_TIMESTAMP(play_time)>=".$start_time." AND UNIX_TIMESTAMP(last_alive_time)<=".$end_time.
                         " GROUP BY video_id ";
                $query .= $union_str;
            }
        }
        
        if (empty($query)) return 0;
        $query = substr($query,0,(strlen($query)-strlen($union_str)));
	    $reader = $this->reader->getDbHandle();

        try {
            $statement = $reader->prepare($query);
            $statement->execute();
        }
        catch(Exception $e) {
            return -1;
        }
        $result = $statement->fetch(PDO::FETCH_ASSOC);
        return ($statement->rowCount()>0) ? $statement->rowCount() : 0 ;
    }

    // {{{public function getPageViewVideoRecord($member_id,$start_time,$end_time,$start=0,$limit=0,$sort='DESC') {
    public function getPageViewVideoRecord($member_id,$start_time,$end_time,$start=0,$limit=0,$sort='DESC') {
        if (empty($member_id) || empty($start_time) || empty($end_time)) return false;
        $limit = (empty($limit)) ? 20 : $limit;
        $limit_str = (intval($limit)>0 && intval($start)>=0) ? " LIMIT $start,$limit " : null;
                $sort = (empty($sort)) ? 'DESC' : $sort;

        $all_tables_list = $this->getScopeOfTime($this->getTableNameOfFpVideoAccess(),$start_time,$end_time);
        if (empty($all_tables_list)) return false;

        $all_exist_tables = self::checkMergeTablesList($this->getTableNameOfFpVideoAccess());
        if (empty($all_exist_tables)) return false;

        $query = '';
        $union_str = ' UNION ALL ';
        foreach($all_tables_list as $key=>$val) {
            if (in_array($val,$all_exist_tables)) {
                $query .= "SELECT id,last_modify_time,member_id,video_id,video_title,programme_id,programme_name,episode_id,play_time,last_alive_time ".
                         " FROM ".$val.
                         " WHERE member_id='".$member_id."' AND UNIX_TIMESTAMP(play_time)>=".$start_time." AND UNIX_TIMESTAMP(last_alive_time)<=".$end_time.
                         " GROUP BY video_id ";
                         //" ORDER BY play_time ".$sort;
                $query .= $union_str;
            }
        }

        if (empty($query)) return false;
        $query = substr($query,0,(strlen($query)-strlen($union_str))).' ORDER BY play_time '.$sort.$limit_str;
        $reader = $this->reader->getDbHandle();
        try {
            $statement = $reader->prepare($query);
            $statement->execute();
        }
        catch(Exception $e) {
            return -1;
        }

        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
            $total = $this->getPageViewVideoRecordCount($member_id,$start_time,$end_time);
        return array('total'=>$total,'name'=>$this->getTableNameOfVideoAccess(),'data'=>$result);
    }
        // }}}

    //get the view video records as the parameters.
    public function getViewVideoRecord($member_id,$start_time,$end_time,$start=0,$limit=0,$sort='DESC') {
        if (empty($member_id) || empty($start_time) || empty($end_time)) return false;
        $limit = (empty($limit)) ? 20 : $limit;
        $limit_str = (intval($limit)>0 && intval($start)>=0) ? " LIMIT $start,$limit " : null;
		$sort = (empty($sort)) ? 'DESC' : $sort;

        $all_tables_list = $this->getScopeOfTime($this->getTableNameOfVideoAccess(),$start_time,$end_time);
        if (empty($all_tables_list)) return false;
        
        $all_exist_tables = self::checkMergeTablesList($this->getTableNameOfVideoAccess());
        if (empty($all_exist_tables)) return false;
        
        $query = '';
        $union_str = ' UNION ALL ';
        foreach($all_tables_list as $key=>$val) {
            if (in_array($val,$all_exist_tables)) {
                $query .= "SELECT id,last_modify_time,member_id,video_id,video_title,programme_id,programme_name,episode_id,play_time,last_alive_time ".
                         " FROM ".$val.
                         " WHERE member_id='".$member_id."' AND UNIX_TIMESTAMP(play_time)>=".$start_time." AND UNIX_TIMESTAMP(last_alive_time)<=".$end_time.
                         " GROUP BY video_id ";
                         //" ORDER BY play_time ".$sort;
                $query .= $union_str;
            }
        }

        if (empty($query)) return false;
        $query = substr($query,0,(strlen($query)-strlen($union_str))).' ORDER BY play_time '.$sort.$limit_str;
        $reader = $this->reader->getDbHandle();
        try {
            $statement = $reader->prepare($query);
            $statement->execute();
        }
        catch(Exception $e) {
            return -1;
        }
        
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
	    $total = $this->getViewVideoRecordCount($member_id,$start_time,$end_time);
        return array('total'=>$total,'name'=>$this->getTableNameOfVideoAccess(),'data'=>$result);	
    }


	//get the number of records from sign in&out.
	public function getSignInOutCount($member_id,$start_time,$end_time) {
		$query = "SELECT id FROM ".$this->getTableNameOfSignInOut().
				 " WHERE login_id='".$member_id."' AND UNIX_TIMESTAMP(create_time)>=".$start_time." AND UNIX_TIMESTAMP(create_time)<=".$end_time;
		
		$reader = $this->reader->getDbHandle();
		try {
			$statement = $reader->prepare($query);
			$statement->execute();
		}
		catch(Exception $e) {
			return -1;
		}

		$result = $statement->fetch(PDO::FETCH_ASSOC);
		return ($statement->rowCount()>0) ? $statement->rowCount() : 0;
	}
    
	//get the sign in and out records as the parameters.
	public function getSignInOutRecord($member_id,$start_time,$end_time,$start=0,$limit=0,$sort='DESC') {
		if (empty($member_id) || empty($start_time) || empty($end_time)) return false;
        $limit = (empty($limit)) ? 20 : $limit;
        $limit_str = (intval($limit)>0 && intval($start)>=0) ? " LIMIT $start,$limit " : null;
		$sort = (empty($sort)) ? 'DESC' : $sort;

		$query = "SELECT id,login_id,login_time,logout_time ".
				 " FROM ".$this->getTableNameOfSignInOut().
				 " WHERE login_id='".$member_id."' AND UNIX_TIMESTAMP(create_time)>=".$start_time." AND UNIX_TIMESTAMP(create_time)<=".$end_time.
				 " ORDER BY create_time ".$sort.$limit_str;
		$reader = $this->reader->getDbHandle();
		try {
			$statement = $reader->prepare($query);
			$statement->execute();
		}
		catch(Exception $e) {
			return -1;
		}
		
		$result = $statement->fetchAll(PDO::FETCH_ASSOC);
		$total = $this->getSignInOutCount($member_id,$start_time,$end_time);
		return array('total'=>$total,'name'=>$this->getTableNameOfSignInOut(),'data'=>$result);
	}

	
	//get the watch frequency of videos by video id.
    public function getRecordRows($query) {
        $data = array();
        if (empty($query)) return 0;
        $reader = $this->reader->getDbHandle();
        try {
            $statement = $reader->prepare($query);
            $statement->execute();
        }
        catch(Exception $e) {
            return -1;
        }

        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        return ($statement->rowCount() > 0) ? $result : $data;
    }

	
	//get the watch frequency of videos by video ids.
    public function getVideosViewCount($condition,$sort,$offset,$limit) {
        $data = array();
        $result = array();
        if ($sort != 'online_time') {
            $sort_str1 = ($sort=='view') ? ' ORDER BY view DESC ' : null;
            $sort_str2 = ($sort=='favour') ? ' ORDER BY favour DESC ' : null;
            $group_by = ' GROUP BY video_id,category_path ';
        }
        else {
            $sort_str = ' ORDER BY online_datetime DESC ';
            $limit_str = null;
            $group_by = null;
        }
        
        //count the count_type=1(view)
        $query_view = 'SELECT category_path,video_id,online_datetime,count(id) as view FROM '.$this->getTableNameOfFpVideoAccess().
                      ' WHERE count_type=1 '.$condition.$group_by.$sort_str1;

        $query_favour = 'SELECT category_path,video_id,online_datetime,count(id) as favour FROM '.$this->getTableNameOfFpVideoAccess().
                        ' WHERE count_type=2 '.$condition.$group_by.$sort_str2;
        $data_view = $this->getRecordRows($query_view);
        $data_favour = $this->getRecordRows($query_favour);
        $tmp_result = array();
        foreach($data_view as $key=>$val) {
            $category_path = $val['category_path'];
            $video_id = $val['video_id'];
            if (!isset($tmp_result[$category_path][$video_id]['favour']) || empty($tmp_result[$category_path][$video_id]['favour'])) {
                $tmp_result[$category_path][$video_id]['favour'] = 0;
            }
            $tmp_result[$category_path][$video_id]['view'] = $val['view'];
            $tmp_result[$category_path][$video_id]['online_datetime'] = $val['online_datetime'];
        }

        foreach($data_favour as $key=>$val) {
            $category_path = $val['category_path'];
            $video_id = $val['video_id'];
            if (!isset($tmp_result[$category_path][$video_id]['view']) || empty($tmp_result[$category_path][$video_id]['view'])) {
                $tmp_result[$category_path][$video_id]['view'] = 0;
            }
            if ($tmp_result[$category_path][$video_id]['online_datetime'] < $val['online_datetime']) {
                $tmp_result[$category_path][$video_id]['online_datetime'] = $val['online_datetime'];
            }
            $tmp_result[$category_path][$video_id]['favour'] = $val['favour'];
        }
        
        $offset_data = array();
        foreach($tmp_result as $key=>$value) {
            foreach($value as $vid=>$val) {
                $offset_data[] = array('vid'=>$vid,'path'=>$key);   
            }
        }


        //intergration data.
        //array('drama'=>array('100001'=>array('view'=>xx,'favour'=>xx,'online_datetime'=>xxxx)));
        $data = array();
        $start = ($offset-1)*$limit;
        $end = $start+$limit;
        for ($i=$start;$i<$end;$i++) {
            if (!isset($offset_data[$i])) continue;
            $tmp = $offset_data[$i];
            $category_path = $tmp['path'];
            $video_id = $tmp['vid'];
            $tmp_fore = $tmp_result[$category_path][$video_id];
            $data[$category_path][$video_id] = array('view'=>$tmp_fore['view'],'favour'=>$tmp_fore['favour'],'online_datetime'=>$tmp_fore['online_datetime']);

        }
        

        return array('records'=>$data,'total'=>count($offset_data));
    }

	
	//get the watch frequency of programmes by programme ids.
    public function getProgrammesViewCount($programme_id,$sort,$offset,$limit) {
        $data = array();
        $result = array();
        if ($sort != 'online_time') {
            $sort_str1 = ($sort=='view') ? ' ORDER BY view DESC ' : null;
            $sort_str2 = ($sort=='favour') ? ' ORDER BY favour DESC ' : null;
            $group_by = ' GROUP BY programme_id,category_path ';
        }
        else {
            $sort_str = ' ORDER BY online_datetime DESC ';
            $limit_str = null;
            $group_by = null;
        }
        
        //count the count_type=1(view)
        $query_view = 'SELECT category_path,programme_id,online_datetime,count(id) as view FROM '.$this->getTableNameOfFpVideoAccess().
                      ' WHERE count_type=1 '.$condition.$group_by.$sort_str1;

        $query_favour = 'SELECT category_path,programme_id,online_datetime,count(id) as favour FROM '.$this->getTableNameOfFpVideoAccess().
                        ' WHERE count_type=2 '.$condition.$group_by.$sort_str2;
        $data_view = $this->getRecordRows($query_view);
        $data_favour = $this->getRecordRows($query_favour);
        $tmp_result = array();
        foreach($data_view as $key=>$val) {
            $category_path = $val['category_path'];
            $programme_id = $val['programme_id'];
            if (!isset($tmp_result[$category_path][$programme_id]['favour']) || empty($tmp_result[$category_path][$programme_id]['favour'])) {
                $tmp_result[$category_path][$programme_id]['favour'] = 0;
            }
            $tmp_result[$category_path][$programme_id]['view'] = $val['view'];
            $tmp_result[$category_path][$programme_id]['online_datetime'] = $val['online_datetime'];
        }

        foreach($data_favour as $key=>$val) {
            $category_path = $val['category_path'];
            $programme_id = $val['programme_id'];
            if (!isset($tmp_result[$category_path][$programme_id]['view']) || empty($tmp_result[$category_path][$programme_id]['view'])) {
                $tmp_result[$category_path][$programme_id]['view'] = 0;
            }
            if ($tmp_result[$category_path][$programme_id]['online_datetime'] < $val['online_datetime']) {
                $tmp_result[$category_path][$programme_id]['online_datetime'] = $val['online_datetime'];
            }
            $tmp_result[$category_path][$programme_id]['favour'] = $val['favour'];
        }

        $offset_data = array();
        foreach($tmp_result as $key=>$value) {
            foreach($value as $vid=>$val) {
                $offset_data[] = array('pid'=>$vid,'path'=>$key);   
            }
        }

        //intergration data.
        //array('drama'=>array('100001'=>array('view'=>xx,'favour'=>xx,'online_datetime'=>xxxx)));
        $data = array();
        $start = ($offset-1)*$limit;
        $end = $start+$limit;
        for ($i=$start;$i<$end;$i++) {
            if (!isset($offset_data[$i])) continue;
            $tmp = $offset_data[$i];
            $category_path = $tmp['path'];
            $programme_id = $tmp['pid'];
            $tmp_fore = $tmp_result[$category_path][$programme_id];
            $data[$category_path][$programme_id] = array('view'=>$tmp_fore['view'],'favour'=>$tmp_fore['favour'],'online_datetime'=>$tmp_fore['online_datetime']);

        }

        return array('records'=>$data,'total'=>count($offset_data));
    }

    //get the video stats
    public function getVideoStatsByTypeAndInterval($type,$start_time,$end_time,$order) {
        $data = array();
		$order_string = null;
        $show_fields = ($type==1) ? 'video_id' : (($type==2) ? 'programme_id' : 'video_id');
		
		foreach($order as $order_k=>$order_d) {
			if (strtolower($order_k)!='view' && strtolower($order_k)!='favour') continue;
			$tmp_order = (strtolower($order_d)!='asc' && strtolower($order_d)!='desc') ? 'DESC' : strtoupper($order_d);
			$tmp_order_field = (strtolower($order_k)=='view') ? 'viewing' : $order_k;
			$order_string.= $tmp_order_field.' '.$tmp_order.',';
		}
		$order_string = (!empty($order_string)) ? substr($order_string,0,strlen($order_string)-1) : $show_fields.' ASC';

        $query = "SELECT ".$show_fields.",sum(viewing) as viewing,sum(favour) as favour ".
                 " FROM ".$this->getTableNameOfFpVideoAccessWS().
                 " WHERE last_modify_time>='".$start_time."' AND last_modify_time<='".$end_time."' ".
				 " GROUP BY ".$show_fields.
                 " ORDER BY ".$order_string;
		$reader = $this->reader->getDbHandle();
        try {
            $statement = $reader->prepare($query);
            $statement->execute();
        }
        catch(Exception $e) {
            return -1;
        }
        $result = $statement->fetchAll(PDO::FETCH_ASSOC);
        foreach($result as $key=>$value) {
            $type_ = $value[$show_fields];
            if (!isset($data[$type_]['view'])) {
                $data[$type_]['view'] = 0;
            }
            if (!isset($data[$type_]['favour'])) {
                $data[$type_]['favour'] = 0;
            }
            $data[$type_]['view'] += $value['viewing'];
            $data[$type_]['favour'] += $value['favour'];
        }
		return $data;
    }
    
    //get video viewing and favour count.
    public function getVideoViewingFavourStat($time_bucket,$type,$video_ids,$category_ids,$period,$offset,$limit,$order) {
        $table_name = $this->getTableNameOfVideoViewFavourStat();
        $time_bucket_fileds = $time_bucket[$period];
        $time_bucket_field = null;
        foreach($time_bucket_fileds as $item) {
            if (preg_match("/view/",$item)) $alias_name = 'view';
            if (preg_match("/fav/",$item)) $alias_name = 'favour';
            $time_bucket_field .= "SUM(".$item.") AS ".$alias_name.",";
        }
        $time_bucket_field = substr($time_bucket_field,0,strlen($time_bucket_field)-1);
        $another_field = ($type==1 || $type!=2) ? 'video_id' : (($type==2) ? 'programme_id' : 'video_id');
        $condition = ' WHERE 1=1 ';
        $condition .= 'AND video_id NOT IN (\'0\') ';
        if (!empty($category_ids)) {
            $condition .= ' AND category_id IN ('.implode(',',$category_ids).') ';
        }
        if (!empty($video_ids) && is_array($video_ids)) {
            $condition .= ' AND video_id IN ('.implode(',',$video_ids).') ';
        }
        
		foreach($order as $order_k=>$order_d) {
			if (strtolower($order_k)!='view' && strtolower($order_k)!='favour') continue;
			$tmp_order = (strtolower($order_d)!='asc' && strtolower($order_d)!='desc') ? 'DESC' : strtoupper($order_d);
			$order_string.= $order_k.' '.$tmp_order.',';
		}
		$order_string = (!empty($order_string)) ? substr($order_string,0,strlen($order_string)-1) : ' id ASC';
        
        $query_total = "SELECT count(id) AS Total FROM ".$table_name.$condition." GROUP BY ".$another_field;
        $total = count($this->getRecordRows($query_total));

        $query = "SELECT id,".$another_field." AS field_id,".$time_bucket_field.
                 " FROM ".$table_name.$condition.
                 " GROUP BY ".$another_field.
                 " ORDER BY ".$order_string.
                 " LIMIT ".$offset.",".$limit;
        $data = $this->getRecordRows($query);
        return array('records'=>$data,'total'=>$total);
    }


}

?>
